const { query: dbQuery } = require('../config/database');
const { logger } = require('../utils/logger');
const levelService = require('./levelService');

function addYears(date, years = 1) {
  const d = new Date(date);
  d.setFullYear(d.getFullYear() + years);
  return d;
}

async function getOrderNonVoucherTotals(orderId) {
  const rows = await dbQuery(
    `SELECT 
       COALESCE(SUM(oi.total_price), 0) AS gross,
       COALESCE(SUM(CASE WHEN mi.is_cash_voucher = 0 OR mi.is_cash_voucher IS NULL THEN oi.total_price ELSE 0 END), 0) AS non_voucher_gross
     FROM order_items oi
     LEFT JOIN menu_items mi ON oi.menu_item_id = mi.id
     WHERE oi.order_id = ?`,
    [orderId]
  );
  const gross = Number(rows[0]?.gross || 0);
  const nonVoucherGross = Number(rows[0]?.non_voucher_gross || 0);
  return { gross, nonVoucherGross };
}

module.exports = {
  /**
   * 入账（通用）
   */
  async addPoints(userId, amount, { reason = '系统奖励', source = 'admin', referenceId = null, orderId = null, type = 'earn', expiresInYears = 1 } = {}) {
    const points = Math.floor(amount);
    if (!points || points === 0) return { success: false, message: 'amount must be non-zero integer after floor' };

    const expiresAt = type === 'earn' ? addYears(new Date(), expiresInYears) : null;

    const conn = await require('../config/database').pool.getConnection();
    try {
      await conn.beginTransaction();

      // 插入积分流水
      const [ins] = await conn.execute(
        `INSERT INTO points_history (user_id, points_change, type, reason, source, reference_id, order_id, status, created_at, expires_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, 'active', NOW(), ?)`,
        [userId, points, type, reason, source, referenceId, orderId, expiresAt]
      );

      // 更新用户积分余额（earn正向加，spend/expire/adjust可为负）
      await conn.execute(
        `UPDATE users SET points_balance = points_balance + ?, updated_at = NOW() WHERE user_id = ?`,
        [points, userId]
      );

      await conn.commit();

      // 仅在正向earn时评估等级
      if (type === 'earn' && points > 0) {
        try { await levelService.evaluateAndUpdateUserLevel(userId); } catch (e) { logger.warn('Update level after addPoints failed:', e.message); }
      }

      return { success: true, insertId: ins.insertId };
    } catch (err) {
      await conn.rollback();
      // 可能违反唯一约束(重复订单入账)
      if (err && (err.code === 'ER_DUP_ENTRY' || String(err.message || '').includes('UNIQUE'))) {
        logger.warn('Duplicate points add ignored:', { userId, source, referenceId });
        return { success: true, duplicate: true };
      }
      logger.error('addPoints failed:', err);
      throw err;
    } finally {
      conn.release();
    }
  },

  /**
   * 扣减（消费或过期）
   */
  async deductPoints(userId, amount, { reason = '积分扣减', source = 'spend', referenceId = null, orderId = null, type = 'spend' } = {}) {
    const points = -Math.abs(Math.floor(amount));
    return this.addPoints(userId, points, { reason, source, referenceId, orderId, type, expiresInYears: 0 });
  },

  /**
   * 订单完成入账
   * - 基准：每实付1美元得1分（向下取整）
   * - 购买现金券不计分；若订单混合，则按非现金券金额占比近似计算
   */
  async awardPointsForOrder(order) {
    try {
      if (!order || !order.id) return { success: false, message: 'invalid order' };

      const userId = order.user_id;
      const amountPaid = Number(order.payment_amount || order.total_amount || 0);
      if (!userId || amountPaid <= 0) return { success: false, message: 'no amount paid' };

      const { gross, nonVoucherGross } = await getOrderNonVoucherTotals(order.id);

      // 如果全是现金券，不入账
      if (gross > 0 && nonVoucherGross <= 0) {
        logger.info(`Order ${order.id} contains only vouchers, skip points earning`);
        return { success: true, skipped: true };
      }

      let base = amountPaid;
      if (gross > 0 && nonVoucherGross > 0 && nonVoucherGross < gross) {
        base = amountPaid * (nonVoucherGross / gross);
      }

      const points = Math.floor(base);
      if (points <= 0) return { success: true, skipped: true };

      return await this.addPoints(userId, points, {
        reason: '订单完成获得积分',
        source: 'purchase',
        referenceId: String(order.id),
        orderId: order.id,
        type: 'earn',
        expiresInYears: 1
      });
    } catch (err) {
      logger.error('awardPointsForOrder failed:', err);
      throw err;
    }
  },

  /**
   * 过期作废：将到期且未作废的正向入账进行一次性负向冲销
   */
  async expireDuePoints(limit = 1000) {
    // 查找到期的earn记录（active）
    const rows = await dbQuery(
      `SELECT id, user_id, points_change FROM points_history 
       WHERE type = 'earn' AND status = 'active' AND expires_at IS NOT NULL AND expires_at <= NOW()
       LIMIT ?`, [limit]
    );

    // 按用户聚合
    const byUser = new Map();
    for (const r of rows) {
      const arr = byUser.get(r.user_id) || [];
      arr.push(r);
      byUser.set(r.user_id, arr);
    }

    // 逐用户处理，减少锁冲突
    for (const [userId, items] of byUser.entries()) {
      const total = items.reduce((s, it) => s + Number(it.points_change || 0), 0);
      const conn = await require('../config/database').pool.getConnection();
      try {
        await conn.beginTransaction();
        // 标记原记录已过期
        const ids = items.map(it => it.id);
        await conn.execute(`UPDATE points_history SET status = 'expired' WHERE id IN (${ids.map(() => '?').join(',')})`, ids);
        // 插入负向冲销记录
        await conn.execute(
          `INSERT INTO points_history (user_id, points_change, type, reason, source, reference_id, order_id, status, created_at)
           VALUES (?, ?, 'expire', '积分到期作废', 'system', NULL, NULL, 'active', NOW())`,
          [userId, -Math.abs(total)]
        );
        // 扣减用户积分余额
        await conn.execute(`UPDATE users SET points_balance = points_balance - ?, updated_at = NOW() WHERE user_id = ?`, [Math.abs(total), userId]);
        await conn.commit();
      } catch (err) {
        await conn.rollback();
        logger.error('expireDuePoints failed for user', userId, err);
      } finally {
        conn.release();
      }
    }

    return { success: true, processedUsers: byUser.size, processedRecords: rows.length };
  }
};